package co.gov.medellin.ssti.sub.paoyer.administration.dao;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.CostCenterByAuthorizedVO;

/**
 * DAO para crear, consultar y actualizar registros en la vista
 * VFD_CENCOSTOS_X_AUTORIZADO
 * 
 * @author Rene.Rangel@consultsoft.com
 * @version 1.0
 */

public class CostCenterByAuthorizedDAO {
	/**
	 * Conexion a la base de datos.
	 */
	private Connection conn = null;

	/**
	 * Constructor que inicializa la conexion con la base de datos.
	 * 
	 * @param conn
	 *            Conexion con la base de datos.
	 */

	public CostCenterByAuthorizedDAO(Connection conn) {
		this.conn = conn;
	}

	/**
	 * Consulta un grupo de registros de la vista VFD_CENCOSTOS_X_AUTORIZADO a
	 * partir de las condiciones que vienen en el Value Object
	 * 
	 * @param CostCenterByAuthorizedVO
	 *            Value Object con las condiciones a tener encuenta.
	 * 
	 * @return Collection Arreglo de registros retornados de la consulta.
	 */
	public Collection<CostCenterByAuthorizedVO> findCostCenterAuthorizedByCriterea(
			CostCenterByAuthorizedVO criterea) throws SQLException {

		/**
		 * Se consulta sobre la vista creada para este fin
		 */

		String query = "SELECT ccau.* "
				+ "FROM vfd_cencostos_x_autorizado ccau ";

		String filters = "";
		// Filtro por Codigo Interno del Centro de Costos
		if ((criterea.getCostCenterInternalCode() != null)
				&& !(criterea.getCostCenterInternalCode().intValue() == 0)) {
			if (filters.equals("")) {
				filters = filters + " ccau.ceco_codin = '"
						+ criterea.getCostCenterInternalCode();
			} else {
				filters = filters + " AND ccau.ceco_codin = '"
						+ criterea.getCostCenterInternalCode();
			}
		}

		// Filtro por Codigo Usuario de Centro de Costos
		if ((criterea.getCostCenterCustomCode() != null)
				&& !(criterea.getCostCenterCustomCode().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " ccau.ceco_codusu = '"
						+ criterea.getCostCenterCustomCode() + "'";
			} else {
				filters = filters + " AND ccau.ceco_codusu = '"
						+ criterea.getCostCenterCustomCode() + "'";
			}
		}

		// Filtro por Nombre del Centro de Costos
		if ((criterea.getCostCenterName() != null)
				&& !(criterea.getCostCenterName().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " ccau.ceco_nombre LIKE '%"
						+ criterea.getCostCenterName() + "%'";
			} else {
				filters = filters + " AND ccau.ceco_nombre LIKE '%"
						+ criterea.getCostCenterName() + "%'";
			}
		}

		// Filtro por Centro de Costo Activo
		if ((criterea.getActiveCostCenter() != null)
				&& !(criterea.getActiveCostCenter().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " ccau.ceco_activo = '"
						+ criterea.getActiveCostCenter().toUpperCase() + "'";
			} else {
				filters = filters + " AND ccau.ceco_activo = '"
						+ criterea.getActiveCostCenter().toUpperCase() + "'";
			}
		}

		// Filtro por Codigo Interno del Autorizado
		if ((criterea.getAuthorizedInternalCode() != null)
				&& !(criterea.getAuthorizedInternalCode().intValue() == 0)) {
			if (filters.equals("")) {
				filters = filters + " ccau.autr_codusu = "
						+ criterea.getAuthorizedInternalCode();
			} else {
				filters = filters + " AND ccau.autr_codusu = "
						+ criterea.getAuthorizedInternalCode();
			}
		}

		// Filtro por Codigo Usuario del Autorizado
		if ((criterea.getAuthorizedCustomCode() != null)
				&& !(criterea.getAuthorizedCustomCode().intValue() == 0)) {
			if (filters.equals("")) {
				filters = filters + " ccau.autr_codusu = "
						+ criterea.getAuthorizedCustomCode();
			} else {
				filters = filters + " AND ccau.autr_codusu = "
						+ criterea.getAuthorizedCustomCode();
			}
		}

		// Filtro por Nombre del Autorizado
		if ((criterea.getAuthorizedName() != null)
				&& !(criterea.getAuthorizedName().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " ausu.autr_nombre LIKE '%"
						+ criterea.getAuthorizedName() + "%'";
			} else {
				filters = filters + " AND ausu.autr_nombre LIKE '%"
						+ criterea.getAuthorizedName() + "%'";
			}
		}

		// Filtro por Autorizado Activo
		if ((criterea.getActiveAuthorized() != null)
				&& !(criterea.getActiveAuthorized().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " ausu.autr_activo = '"
						+ criterea.getActiveAuthorized() + "'";
			} else {
				filters = filters + " AND ausu.autr_activo = '"
						+ criterea.getActiveAuthorized() + "'";
			}
		}

		// Filtro por Clave de Autorizado
		if ((criterea.getAuthorizedPassword() != null)
				&& !(criterea.getAuthorizedPassword().equals(""))) {
			if (filters.equals("")) {
				filters = filters + " ausu.autr_clave = '"
						+ criterea.getAuthorizedPassword() + "'";
			} else {
				filters = filters + " AND ausu.autr_clave = '"
						+ criterea.getAuthorizedPassword() + "'";
			}
		}

		// Si hay filtros, entonces armar la clausula WHERE
		if (!filters.equals("")) {
			query = query + " WHERE " + filters;
		}

		Statement stm = null;
		ResultSet rst = null;
		ArrayList<CostCenterByAuthorizedVO> CostCentersByAuthorized = new ArrayList<CostCenterByAuthorizedVO>();

		stm = conn.createStatement();
		rst = stm.executeQuery(query);

		while (rst.next()) {
			CostCenterByAuthorizedVO costCenterByAuthorized = new CostCenterByAuthorizedVO();
			costCenterByAuthorized.setCostCenterInternalCode(new Long(rst
					.getLong("ceco_codin")));
			costCenterByAuthorized.setCostCenterCustomCode(rst
					.getString("ceco_codusu"));
			costCenterByAuthorized.setCostCenterName(rst
					.getString("ceco_nombre"));
			costCenterByAuthorized.setActiveCostCenter(rst
					.getString("ceco_activo"));
			costCenterByAuthorized.setAuthorizedInternalCode(new Long(rst
					.getLong("autr_codin")));
			costCenterByAuthorized.setAuthorizedCustomCode(new Long(rst
					.getLong("autr_codusu")));
			costCenterByAuthorized.setAuthorizedName(rst
					.getString("autr_nombre"));
			costCenterByAuthorized.setActiveAuthorized(rst
					.getString("autr_activo"));
			costCenterByAuthorized.setAuthorizedPassword(rst
					.getString("autr_clave"));

			CostCentersByAuthorized.add(costCenterByAuthorized);
		}

		stm.close();

		return CostCentersByAuthorized;
	}

}